package com.alpesressources.comexpert.dao;

import com.alpesressources.comexpert.bo.Config;
import com.alpesressources.comexpert.bo.Schema;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.UUID;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import org.apache.log4j.Logger;

import com.alpesressources.comexpert.bo.request.Request;
import com.alpesressources.comexpert.exception.DaoException;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

public class DaoManager {

	private static Logger logger = Logger.getLogger(DaoManager.class);
	
	private String dossier;
	
	private static final String checkUserPassword = "SELECT code FROM utilisateur WHERE code = ? AND CAST(MD5(CONCAT(?, '_', passWord)) AS CHAR) = ?";
	private static final String getVersion = "SELECT VERSION() AS version";
	private static final String getConfig = "SELECT * FROM coMexpert.config WHERE dossier=?";

	private DataSource ds;

	/**
	 *
	 * @param dossier
	 * @throws DaoException
	 */
	public DaoManager(String dossier) throws DaoException {
		Context initContext;

		this.dossier = dossier;

		try {
			initContext = new InitialContext();
			ds = (DataSource) initContext
					.lookup("java:comp/env/jdbc/coMexpertReports");

		} catch (NamingException e) {
			logger.error(e);

			throw new DaoException(e);
		}

	}

	/**
	 *
	 * @param userName
	 * @param passWord
	 * @return
	 * @throws DaoException
	 */
	public String authenticate(String userName, String passWord) throws DaoException {
		String token = null;
		
		try {
			Connection conn = ds.getConnection();
			PreparedStatement stmt = conn.prepareStatement("use " + dossier);

			stmt.execute();
			stmt.close();

			stmt = conn.prepareStatement(checkUserPassword);
			stmt.setString(1, userName);
			stmt.setString(2, Request.SALT);
			stmt.setString(3, passWord);
			ResultSet rs = stmt.executeQuery();

			if (rs.next()) {
				token = UUID.randomUUID().toString();
				logger.info("Authentification ok");
			} else {
				logger.info("Authentification failed: incorrect user or password");
			}

			rs.close();
			stmt.close();
			conn.close();
		} catch(SQLException e) {
			logger.error(e);
			
			throw new DaoException(e);
		}
                
		return token;
	}
	
	public String getDatabaseVersion() throws DaoException {
		String version = null;
		
		try {
			Connection conn = ds.getConnection();
			PreparedStatement stmt = conn.prepareStatement("use " + dossier);

			stmt.execute();
			stmt.close();

			stmt = conn.prepareStatement(getVersion);
			ResultSet rs = stmt.executeQuery();

			if (rs.next()) {
				version = rs.getString(1);
				logger.info("Get database version ok");
			} else {
				logger.info("Get database version nok");
			}

			rs.close();
			stmt.close();
			conn.close();
		} catch(SQLException e) {
			logger.error(e);
			
			throw new DaoException(e);
		}
                
		return version;
	}
	
	public Config getConfig() throws DaoException {
		Config config = null;
		
		try {
			Connection conn = ds.getConnection();
			PreparedStatement stmt = conn.prepareStatement("use " + dossier);

			stmt.execute();
			stmt.close();

			stmt = conn.prepareStatement(getConfig);
			stmt.setString(1, dossier);
			ResultSet rs = stmt.executeQuery();

			if (rs.next()) {
				config = new Config();
				
				config.setMysqlHost(rs.getString("MySQLHost"));
				config.setMysqlUser(rs.getString("MySQLUser"));
				config.setMysqlPassword(rs.getString("MySQLPassWord"));
				config.setMysqlDatabaseName(rs.getString("MySQLDBName"));
				config.setHttpHost(rs.getString("HTTPHost"));
				
				logger.info("Get config ok");
			} else {
				logger.info("Get config nok");
			}

			rs.close();
			stmt.close();
			conn.close();
		} catch(SQLException e) {
			logger.error(e);
			
			throw new DaoException(e);
		}
                
		return config;
	}
	
	public Map<String, Schema> getSchema(String tableName) throws DaoException {
		String getSchema = "SELECT COLUMN_NAME, COLUMN_COMMENT, IFNULL(schema.attribut, 0) AS att, "
			+"IFNULL(schema.condition, '') AS cond, IFNULL(schema.textCondition, '') AS textCondition, "
			+"IFNULL(COLUMN_DEFAULT, '') AS def, IFNULL(NUMERIC_SCALE, 0) as nbD, "
			+"IFNULL(schema.formule, '') AS form, IFNULL(schema.evalTime, 0) AS et, "
			+"IFNULL(DATA_TYPE, '') AS dataTye, IFNULL(CHARACTER_MAXIMUM_LENGTH, 0) AS len "
			+"FROM information_schema.COLUMNS LEFT JOIN "+dossier+".schema ON TABLE_NAME=`table` AND COLUMN_NAME=champ "
			+"WHERE TABLE_SCHEMA=? AND TABLE_NAME=?";
		
		Map<String, Schema> schemas = null;
		
		try {
			Connection conn = ds.getConnection();
			PreparedStatement stmt = conn.prepareStatement("use " + dossier);

			stmt.execute();
			stmt.close();

			stmt = conn.prepareStatement(getSchema);
			stmt.setString(1, dossier);
			stmt.setString(2, tableName);
			ResultSet rs = stmt.executeQuery();
			
			schemas=new HashMap<String, Schema>();

			while (rs.next()) {
				Schema schema = new Schema();
				
				schema.setChampDescription(rs.getString("COLUMN_COMMENT"));
				schema.setAttribut(rs.getInt("att"));
				schema.setCondition(rs.getString("cond"));
				schema.setTextCondition(rs.getString("textCondition"));
				schema.setDefaut(rs.getString("def"));
				schema.setNbDecimal(rs.getInt("nbD"));
				schema.setFormule(rs.getString("form"));
				schema.setLen(rs.getInt("len"));
				schema.setEvalTime(rs.getInt("et"));
				
				schemas.put(rs.getString("COLUMN_NAME"), schema);
			} 

			rs.close();
			stmt.close();
			conn.close();
		} catch(SQLException e) {
			logger.error(e);
			
			throw new DaoException(e);
		}
                
		return schemas;
	}
}
